/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.cbsrs.controller;

import com.cbsrs.model.CollegeSubjectOffering;
import com.cbsrs.model.SubjectSchedules;
import com.cbsrs.utils.ConnectionDAO;
import com.itextpdf.text.log.LoggerFactory;
import com.opensymphony.xwork2.ActionSupport;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Level;
import org.apache.log4j.Logger;

/**
 *
 * @author Arnold
 */
public class SubjectScheduleDAO extends ConnectionDAO {

    private static final Logger log = Logger.getLogger(SubjectScheduleDAO.class);

    public static List<SubjectSchedules> findAll(int blockID) throws Exception {

        List<SubjectSchedules> scheduleList = new ArrayList<SubjectSchedules>();
        String sql = null;

            sql =
                    "SELECT\n"
                    + " b.block_name,\n"
                    + " b.year,\n"
                    + " IF(b.semester = 1, '1st Semester',IF(b.semester = 2, '2nd Semester','Summer')) as semester,\n"
                    + " CONCAT(\n"
                    + "		CONCAT_WS(\n"
                    + "			' - ',\n"
                    + "			DATE_FORMAT(so.start_time, '%h:%i'),\n"
                    + "			DATE_FORMAT(so.end_time, '%h:%i')\n"
                    + "		),\n"
                    + "		', ',\n"
                    + "		CONCAT_WS(\n"
                    + "			'',\n"
                    + "		IF (so.M = 1, 'M', ''),\n"
                    + "	IF (so.T = 1, 'T', ''),\n"
                    + "IF (so.W = 1, 'W', ''),\n"
                    + "IF (so.Th = 1, 'Th', ''),\n"
                    + "IF (so.F = 1, 'F', ''),\n"
                    + "IF (so.Sa = 1, 'Sa', ''),\n"
                    + "IF (so.Su = 1, 'Su', '')\n"
                    + "		),\n"
                    + "		', ',\n"
                    + "		IFNULL(r.room_name,'')) AS SCHEDULE_NAME,\n"
                    + "	s.subject_description,\n"
                    + "IF (\n"
                    + "	so.subject_name = s.subject_name,\n"
                    + "	s.subject_lecunits,\n"
                    + "	s.subject_labunits\n"
                    + ") AS units,\n"
                    + "so.*\n"
                    + "FROM\n"
                    + "	(\n"
                    + "			(\n"
                    + "				schedules so\n"
                    + "				INNER JOIN subjects s ON so.subject_id = s.subject_id\n"
                    + "			)\n"
                    + "			INNER JOIN blocks b ON so.block_id = b.block_id\n"
                    + "		\n"
                    + "	)\n"
                    + "LEFT JOIN rooms r ON r.room_id = so.room_id\n"
                    + "WHERE b.block_id=?";

            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            preparedStatement.setInt(1, blockID);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                SubjectSchedules so = new SubjectSchedules();
                so.setScheduleName(rs.getString("schedule_name"));
                so.setSubjectDescription(rs.getString("subject_description"));
                so.setUnits(rs.getInt("units"));
                so.setScheduleID(rs.getInt("schedule_id"));
                so.setSubjectID(rs.getInt("subject_id"));
                so.setSubjectName(rs.getString("subject_name"));
                so.setBlockID(rs.getInt("block_id"));
                so.setRoomID(rs.getInt("room_id"));
                so.setUserID(rs.getInt("user_id"));
                so.setCreateTs(rs.getString("create_ts"));
                so.setM(rs.getInt("m"));
                so.setT(rs.getInt("t"));
                so.setW(rs.getInt("w"));
                so.setTH(rs.getInt("th"));
                so.setF(rs.getInt("f"));
                so.setSA(rs.getInt("sa"));
                so.setSU(rs.getInt("su"));
                so.setStartTime(rs.getString("start_time"));
                so.setEndTime(rs.getString("end_time"));
                so.setSubjectOfferingID(rs.getInt("subject_offering_id"));
                so.setBlockName(rs.getString("block_name"));
                so.setYear(rs.getInt("year"));
                so.setSemester(rs.getString("semester"));

                scheduleList.add(so);
            }
        return scheduleList;
    }

    public static List<SubjectSchedules> findAll() throws Exception {

        List<SubjectSchedules> soList = new ArrayList<SubjectSchedules>();
        String sql = null;
        sql =
                "SELECT\n"
                + " b.block_name,\n"
                + " b.year,\n"
                + " IF(b.semester = 1, '1st Semester',IF(b.semester = 2, '2nd Semester','Summer')) as semester,\n"
                + " CONCAT(\n"
                + "		CONCAT_WS(\n"
                + "			' - ',\n"
                + "			DATE_FORMAT(so.start_time, '%h:%i'),\n"
                + "			DATE_FORMAT(so.end_time, '%h:%i')\n"
                + "		),\n"
                + "		', ',\n"
                + "		CONCAT_WS(\n"
                + "			'',\n"
                + "		IF (so.M = 1, 'M', ''),\n"
                + "	IF (so.T = 1, 'T', ''),\n"
                + "IF (so.W = 1, 'W', ''),\n"
                + "IF (so.Th = 1, 'Th', ''),\n"
                + "IF (so.F = 1, 'F', ''),\n"
                + "IF (so.Sa = 1, 'Sa', ''),\n"
                + "IF (so.Su = 1, 'Su', '')\n"
                + "		),\n"
                + "		', ',\n"
                + "		IFNULL(r.room_name,'')) AS SCHEDULE_NAME,\n"
                + "	s.subject_description,\n"
                + "IF (\n"
                + "	so.subject_name = s.subject_name,\n"
                + "	s.subject_lecunits,\n"
                + "	s.subject_labunits\n"
                + ") AS units,\n"
                + "so.*\n"
                + "FROM\n"
                + "	(\n"
                + "			(\n"
                + "				schedules so\n"
                + "				INNER JOIN subjects s ON so.subject_id = s.subject_id\n"
                + "			)\n"
                + "			INNER JOIN blocks b ON so.block_id = b.block_id\n"
                + "		\n"
                + "	)\n"
                + "LEFT JOIN rooms r ON r.room_id = so.room_id";

        PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
        log.info("VIEW SUNJECT OFFERINGS" + preparedStatement.toString());
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
            SubjectSchedules so = new SubjectSchedules();

            so.setScheduleName(rs.getString("schedule_name"));
            so.setSubjectDescription(rs.getString("subject_description"));
            so.setUnits(rs.getInt("units"));
            so.setScheduleID(rs.getInt("schedule_id"));
            so.setSubjectID(rs.getInt("subject_id"));
            so.setSubjectName(rs.getString("subject_name"));
            so.setBlockID(rs.getInt("block_id"));
            so.setRoomID(rs.getInt("room_id"));
            so.setUserID(rs.getInt("user_id"));
            so.setCreateTs(rs.getString("create_ts"));
            so.setM(rs.getInt("m"));
            so.setT(rs.getInt("t"));
            so.setW(rs.getInt("w"));
            so.setTH(rs.getInt("th"));
            so.setF(rs.getInt("f"));
            so.setSA(rs.getInt("sa"));
            so.setSU(rs.getInt("su"));
            so.setStartTime(rs.getString("start_time"));
            so.setEndTime(rs.getString("end_time"));
            so.setSubjectOfferingID(rs.getInt("subject_offering_id"));
            so.setBlockName(rs.getString("block_name"));
            so.setYear(rs.getInt("year"));
            so.setSemester(rs.getString("semester"));

            soList.add(so);
        }

        return soList;
    }

    public static List<CollegeSubjectOffering> findAllCollegeSubjectOffering() {

        List<CollegeSubjectOffering> collegeSubjectOfferingList = new ArrayList<CollegeSubjectOffering>();
        String sql = null;

        try {
            sql = "SELECT *\n"
                    + "FROM\n"
                    + "colleges AS c\n"
                    + "INNER JOIN subject_offerings AS so ON so.college_id = c.college_id";

            PreparedStatement preparedStatement = getConnectionObject().prepareStatement(sql);
            log.info(preparedStatement.toString());
            ResultSet rs = preparedStatement.executeQuery();
            while (rs.next()) {
                CollegeSubjectOffering cso = new CollegeSubjectOffering();

                cso.setCollegeID(rs.getInt("college_id"));
                cso.setCollegeName(rs.getString("college_name"));
                cso.setCollegeDescription(rs.getString("college_description"));
                cso.setSubjectOfferingID(rs.getInt("subject_offering_id"));
                cso.setSubjectOfferingStatus(rs.getInt("subject_offering_status"));
                cso.setCollegeSubjectOfferingName(rs.getString("college_name") + " - " + rs.getString("subject_offering_name"));
                cso.setSubjectOfferingName(rs.getString("subject_offering_name"));
                cso.setSubjectOfferingDescription(rs.getString("subject_offering_description"));
                cso.setSubjectOfferingStartTime(rs.getString("subject_offering_starttime"));
                cso.setSubjectOfferingEndTime(rs.getString("subject_offering_endtime"));
                collegeSubjectOfferingList.add(cso);
            }
        } catch (SQLException ex) {
            log.error(ex);
        }

        return collegeSubjectOfferingList;
    }
}
